﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess;
using System.Data;
using System.Collections;
using EntityLibrary;

namespace EntityDaoLibrary
{
    public class MIItemRelationYPDAO : IEntityDAO
    {
        private IDataAccess data;
        public MIItemRelationYPDAO()
        {
        }
        public MIItemRelationYPDAO(IDataAccess data)
        {
            this.data = data;
        }


        #region IEntityDAO 成员

        public void insertEntity(DataSet entityData)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                ParamField[] pars;
                sqlStruct.SqlString = "INSERT INTO I_ITEMRELATIONYP (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3) values (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3)";
                pars = new ParamField[8];
                pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                pars[1] = new ParamField("ITEMIDMED", "ITEMIDMED");
                pars[2] = new ParamField("OPID", "OPID");
                pars[3] = new ParamField("IMPORTDT", "IMPORTDT");
                pars[4] = new ParamField("TRANDT", "TRANDT");
                pars[5] = new ParamField("PRE1", "PRE1");
                pars[6] = new ParamField("PRE2", "PRE2");
                pars[7] = new ParamField("PRE3", "PRE3");
                sqlStruct.ParamFields = pars;
                foreach (DataRow row in entityData.Tables["I_ITEMRELATIONYP"].Rows)
                {
                    data.ExecuteNonQuery(row, sqlStruct);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public void updateEntity(DataSet entityData)
        {
            try
            {
                if (entityData.GetChanges() == null)
                    return;
                SqlStruct sqlStruct = new SqlStruct();
                ParamField[] pars;
                foreach (DataRow row in entityData.GetChanges().Tables["I_ITEMRELATIONYP"].Rows)
                {
                    switch (row.RowState)
                    {
                        case DataRowState.Added:
                            sqlStruct.SqlString = "INSERT INTO I_ITEMRELATIONYP (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3) values (ITEMIDHIS,ITEMIDMED,OPID,IMPORTDT,TRANDT,PRE1,PRE2,PRE3)";
                            pars = new ParamField[8];
                            pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                            pars[1] = new ParamField("ITEMIDMED", "ITEMIDMED");
                            pars[2] = new ParamField("OPID", "OPID");
                            pars[3] = new ParamField("IMPORTDT", "IMPORTDT");
                            pars[4] = new ParamField("TRANDT", "TRANDT");
                            pars[5] = new ParamField("PRE1", "PRE1");
                            pars[6] = new ParamField("PRE2", "PRE2");
                            pars[7] = new ParamField("PRE3", "PRE3");
                            sqlStruct.ParamFields = pars;
                            data.ExecuteNonQuery(row, sqlStruct);
                            break;
                        case DataRowState.Deleted:
                            sqlStruct.SqlString = "DELETE FROM I_ITEMRELATIONYP WHERE ITEMIDHIS=ITEMIDHIS";
                            pars = new ParamField[1];
                            pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                            sqlStruct.ParamFields = pars;
                            row.RejectChanges();
                            data.ExecuteNonQuery(row, sqlStruct);
                            break;
                        case DataRowState.Modified:
                            sqlStruct.SqlString = "UPDATE I_ITEMRELATIONYP SET ITEMIDMED=@ITEMIDMED,OPID=@OPID,IMPORTDT=@IMPORTDT,TRANDT=GETDATE( ),PRE1=@PRE1,PRE2=@PRE2,PRE3=@PRE3 WHERE ITEMIDHIS=@ITEMIDHIS";
                            pars = new ParamField[7];
                            pars[0] = new ParamField("@ITEMIDMED", "ITEMIDMED");
                            pars[1] = new ParamField("@OPID", "OPID");
                            pars[2] = new ParamField("@IMPORTDT", "IMPORTDT");
                            //pars[3]=new ParamField("TRANDT","TRANDT");
                            pars[3] = new ParamField("@PRE1", "PRE1");
                            pars[4] = new ParamField("@PRE2", "PRE2");
                            pars[5] = new ParamField("@PRE3", "PRE3");
                            pars[6] = new ParamField("@ITEMIDHIS", "ITEMIDHIS");
                            sqlStruct.ParamFields = pars;
                            data.ExecuteNonQuery(row, sqlStruct);
                            break;
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public void deleteEntity(DataSet entityData)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                ParamField[] pars;
                sqlStruct.SqlString = "DELETE FROM I_ITEMRELATIONYP WHERE ITEMIDHIS=ITEMIDHIS";
                pars = new ParamField[1];
                pars[0] = new ParamField("ITEMIDHIS", "ITEMIDHIS");
                sqlStruct.ParamFields = pars;
                foreach (DataRow row in entityData.Tables["I_ITEMRELATIONYP"].Rows)
                {
                    data.ExecuteNonQuery(row, sqlStruct);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        public DataSet FindByPrimaryKey(object strKeyValue)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                string sql = "";
                QueryParameterCollection para;
                MIItemRelationYPData ds = new MIItemRelationYPData();
                sql = "SELECT I_ITEMRELATIONYP.ITEMIDHIS,I_ITEMRELATIONYP.ITEMIDMED,I_ITEMRELATIONYP.OPID,I_ITEMRELATIONYP.IMPORTDT,I_ITEMRELATIONYP.TRANDT,I_ITEMRELATIONYP.PRE1,I_ITEMRELATIONYP.PRE2,I_ITEMRELATIONYP.PRE3 from I_ITEMRELATIONYP WHERE ITEMIDHIS=@ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@ITEMIDHIS", strKeyValue);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONYP");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        #endregion
        public DataSet FindByPrimaryKey(ArrayList KeyValue)
        {
            try
            {
                SqlStruct sqlStruct = new SqlStruct();
                string sql = "";
                QueryParameterCollection para;
                MIItemRelationYPData ds = new MIItemRelationYPData();
                sql = "SELECT I_ITEMRELATIONYP.ITEMIDHIS,I_ITEMRELATIONYP.ITEMIDMED,I_ITEMRELATIONYP.OPID,I_ITEMRELATIONYP.IMPORTDT,I_ITEMRELATIONYP.TRANDT,I_ITEMRELATIONYP.PRE1,I_ITEMRELATIONYP.PRE2,I_ITEMRELATIONYP.PRE3 from I_ITEMRELATIONYP WHERE ITEMIDHIS=@ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@ITEMIDHIS", KeyValue[0]);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONYP");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        public DataSet FindAll()
        {
            try
            {
                string sql;
                DataSet ds = new DataSet();
                //sql = "SELECT I_ITEMRELATIONYP.ITEMIDHIS,I_ITEMRELATIONYP.ITEMIDMED,I_ITEMRELATIONYP.OPID,I_ITEMRELATIONYP.IMPORTDT,I_ITEMRELATIONYP.TRANDT,I_ITEMRELATIONYP.PRE1,I_ITEMRELATIONYP.PRE2,I_ITEMRELATIONYP.PRE3 ";
                //sql += " ,T_CODEX_DETAIL.TRADENAME,T_CODEX_DETAIL.SPECS,T_CODEX_DETAIL.PACK,T_CODEX_DETAIL.UNIT,T_CODEX_DETAIL.DOSAGEUNIT,T_CODEX_DETAIL.RetailPrice,T_CODEX_DETAIL.ProductArea,T_CODEX_DETAIL.SpellNo";
                //sql += "	from I_ITEMRELATIONYP,T_CODEX_DETAIL ";
                //sql += " WHERE I_ITEMRELATIONYP.ITEMIDHIS=T_CODEX_DETAIL.LEECHDOMDETAILNO";
                //sql += " ORDER BY I_ITEMRELATIONYP.ITEMIDHIS";

                sql = @"SELECT I_ITEMRELATIONYP.ITEMIDHIS,I_ITEMRELATIONYP.ITEMIDMED,I_ITEMRELATIONYP.OPID,I_ITEMRELATIONYP.IMPORTDT,I_ITEMRELATIONYP.TRANDT,I_ITEMRELATIONYP.PRE1,I_ITEMRELATIONYP.PRE2,I_ITEMRELATIONYP.PRE3
                         ,YK_YPCDMLK.ypmc,YK_YPCDMLK.ggdw,YK_YPCDMLK.ypgg,YK_YPCDMLK.zxdw,YK_YPCDMLK.py
                        from I_ITEMRELATIONYP,YK_YPCDMLK 
                         WHERE I_ITEMRELATIONYP.ITEMIDHIS=YK_YPCDMLK.idm
                        ORDER BY I_ITEMRELATIONYP.ITEMIDHIS";
                data.ExecuteDataset(sql, ds, "I_ITEMRELATIONYP");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }

        }
        /*重载FindAll方法，用来区分门诊和住院，clinicType 1 门诊，2住院
         * 
         * 修改人：郑宝力
         * 
         * 修改日期：2007年6月20日
         * 
         * */
        public DataSet FindAll(int clinicType)
        {
            try
            {
                string sql;
                QueryParameterCollection para;
                MIItemRelationYPData ds = new MIItemRelationYPData();
                sql = "SELECT I_ITEMRELATIONYP.ITEMIDHIS,I_ITEMRELATIONYP.ITEMIDMED,I_ITEMRELATIONYP.OPID,I_ITEMRELATIONYP.IMPORTDT,I_ITEMRELATIONYP.TRANDT,I_ITEMRELATIONYP.PRE1,I_ITEMRELATIONYP.PRE2,I_ITEMRELATIONYP.PRE3 ";
                sql += " ,T_CODEX_DETAIL.TRADENAME,T_CODEX_DETAIL.SPECS,T_CODEX_DETAIL.PACK,T_CODEX_DETAIL.UNIT,T_CODEX_DETAIL.DOSAGEUNIT,T_CODEX_DETAIL.RetailPrice,T_CODEX_DETAIL.ProductArea,T_CODEX_DETAIL.SpellNo";
                sql += "	from I_ITEMRELATIONYP,T_CODEX_DETAIL ";
                sql += " WHERE I_ITEMRELATIONYP.ITEMIDHIS=T_CODEX_DETAIL.LEECHDOMDETAILNO AND I_ITEMRELATIONYP.CLINICTYPE=@CLINICTYPE";
                sql += " ORDER BY I_ITEMRELATIONYP.ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@CLINICTYPE", clinicType);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONYP");
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }

        }
        public string GetItemIDMed(string itemid)
        {
            try
            {
                string sql;
                SqlStruct sqlStruct = new SqlStruct();
                QueryParameterCollection para;
                //MIItemRelationYPData ds=new MIItemRelationYPData();
                DataSet ds = new DataSet();
                sql = "SELECT ITEMIDMED FROM I_ITEMRELATIONYP WHERE ItemIDhis=@ITEMIDHIS";
                para = new QueryParameterCollection(1);
                para.Add("@ITEMIDHIS", itemid);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONYP");
                if (ds.Tables[0].Rows.Count < 1)
                    throw new Exception("未检测到药品项目对应关系信息:" + itemid);
                return ds.Tables[0].Rows[0]["ITEMIDMED"].ToString();

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        /*重载GetItemIDMed方法，用来区分门诊和住院，clinicType 1 门诊，2住院
         * 
         * 修改人：郑宝力
         * 
         * 修改日期：2007年6月20日
         * 
         * */
        public string GetItemIDMed(string itemid, int clinicType)
        {
            try
            {
                string sql;
                SqlStruct sqlStruct = new SqlStruct();
                QueryParameterCollection para;
                //MIItemRelationYPData ds=new MIItemRelationYPData();
                DataSet ds = new DataSet();
                sql = "SELECT ITEMIDMED FROM I_ITEMRELATIONYP WHERE ItemIDhis=@ITEMIDHIS AND CLINICTYPE=@CLINICTYPE";
                para = new QueryParameterCollection(2);
                para.Add("@ITEMIDHIS", itemid);
                para.Add("@CLINICTYPE", clinicType);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONYP");
                if (ds.Tables[0].Rows.Count < 1)
                    throw new Exception("未检测到药品项目对应关系信息:" + itemid);
                return ds.Tables[0].Rows[0]["ITEMIDMED"].ToString();

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        public void ImportNewData()
        {
            try
            {
                string sql;
                SqlStruct sqlStruct = new SqlStruct();
//                sql = @"INSERT INTO I_ITEMRELATIONYP(ITEMIDHIS,IMPORTDT,PRE1,ITEMIDMED,OPID,TRANDT, PRE2,PRE3) 
//                        SELECT idm,GETDATE( ),ypdm,'','',GETDATE( ),'','' FROM YK_YPGGMLK 
//                        WHERE  NOT EXISTS(
//                        SELECT ITEMIDHIS FROM I_ITEMRELATIONYP WHERE I_ITEMRELATIONYP.ITEMIDHIS=YK_YPGGMLK.idm)";

                sql = @"
                        INSERT INTO I_ITEMRELATIONYP(ITEMIDHIS,IMPORTDT,PRE1,ITEMIDMED,OPID,TRANDT, PRE2,PRE3) 
                        SELECT idm,GETDATE( ),ypdm,'','',GETDATE( ),'','' FROM YK_YPCDMLK 
                        WHERE  NOT EXISTS(
                        SELECT ITEMIDHIS FROM I_ITEMRELATIONYP WHERE I_ITEMRELATIONYP.ITEMIDHIS=YK_YPCDMLK.idm)
                        ";
                data.ExecuteNonQuery(sql);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        public DataSet DisplayCorrectInfo(DateTime dt)
        {
            try
            {
                DataSet ds = new DataSet();
                string sql = "";
//                sql = @"SELECT I_ITEMRELATIONYP.ITEMIDHIS,I_ITEMRELATIONYP.ITEMIDMED,I_ITEMRELATIONYP.OPID,I_ITEMRELATIONYP.IMPORTDT,I_ITEMRELATIONYP.TRANDT,I_ITEMRELATIONYP.PRE1,I_ITEMRELATIONYP.PRE2,I_ITEMRELATIONYP.PRE3
//                        ,YK_YPGGMLK.ypmc,YK_YPGGMLK.ggdw,YK_YPGGMLK.ypgg,YK_YPGGMLK.zxdw,YK_YPGGMLK.py,YB_YP_INFO.YPMC,YB_YP_INFO.YPBM,YB_YP_INFO.SFLB,YB_YP_INFO.YPDJ
//                        from I_ITEMRELATIONYP,YK_YPGGMLK ,YB_YP_INFO
//                        WHERE I_ITEMRELATIONYP.ITEMIDHIS=YK_YPGGMLK.idm AND  I_ITEMRELATIONYP.ITEMIDMED=YB_YP_INFO.YPBM AND TRANDT > @dt
//                        ORDER BY I_ITEMRELATIONYP.ITEMIDHIS
//                        ";
//                sql = @"SELECT I_ITEMRELATIONYP.ITEMIDHIS as '医院编码',I_ITEMRELATIONYP.ITEMIDMED as '医保编码',I_ITEMRELATIONYP.TRANDT as '对照时间'
//                        ,YK_YPCDMLK.ypmc as '医院名称',YK_YPCDMLK.ggdw as '剂型',YK_YPCDMLK.ypgg as '规格',YK_YPCDMLK.zxdw as '单位',YK_YPCDMLK.ylsj as '价格',YB_YP_INFO.YPMC as '中心名称',YB_YP_INFO.YPBM as '中心编码',YB_YP_INFO.SFLB as '收费类别',1 as '中心项目类别'
//                        from I_ITEMRELATIONYP,YK_YPCDMLK ,YB_YP_INFO
//                        WHERE I_ITEMRELATIONYP.ITEMIDHIS=YK_YPCDMLK.idm AND  I_ITEMRELATIONYP.ITEMIDMED=YB_YP_INFO.YPBM AND TRANDT >=@dt
//                        ORDER BY I_ITEMRELATIONYP.ITEMIDHIS
//                        ";
                sql = @"SELECT I_ITEMRELATIONYP.ITEMIDHIS as '医院编码',I_ITEMRELATIONYP.ITEMIDMED as '医保编码',I_ITEMRELATIONYP.TRANDT as '对照时间'
                        ,YK_YPCDMLK.ypmc as '医院名称',YK_YPCDMLK.name as '剂型',YK_YPCDMLK.ypgg as '规格',YK_YPCDMLK.zxdw as '单位',YK_YPCDMLK.ylsj as '价格',YB_YP_INFO.YPMC as '中心名称',YB_YP_INFO.YPBM as '中心编码',YB_YP_INFO.SFLB as '收费类别',1 as '中心项目类别'
                        from I_ITEMRELATIONYP,(select YK_YPCDMLK.* ,YK_YPJXK.name from YK_YPCDMLK,YK_YPJXK where YK_YPCDMLK.jxdm=YK_YPJXK.id) as YK_YPCDMLK ,YB_YP_INFO
                        WHERE I_ITEMRELATIONYP.ITEMIDHIS=YK_YPCDMLK.idm AND  I_ITEMRELATIONYP.ITEMIDMED=YB_YP_INFO.YPBM AND TRANDT>=@dt
                        ORDER BY I_ITEMRELATIONYP.ITEMIDHIS
                        ";
                QueryParameterCollection para = new QueryParameterCollection(1);
                para.Add("@dt", dt);
                data.ExecuteDataset(sql, para, ds, "I_ITEMRELATIONYP");
                return ds;
            }
            catch (Exception e)
            {

                throw new Exception(e.Message, e);
            }

        }


    }
}
